package com.xc.common.tool;

import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import javax.servlet.http.HttpServletRequest;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;

import com.xc.common.object.MergedResult;


public class ReportAid {
	private Workbook workbook;
	private CellStyle style;
	public ReportAid() {
		super();
	}
	public ReportAid(Workbook workbook) {
		this.workbook = workbook;
	}
	public CellStyle createHeadCellStyle(){
		CellStyle style = workbook.createCellStyle();
		style.setWrapText(true);
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		return style;
	}
	public CellStyle createCellStyle(){
		CellStyle style = workbook.createCellStyle();
		style.setWrapText(true);
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		Font font = workbook.createFont();
	    font.setFontHeight((short) 200);
	    font.setFontName("方正书宋简体");
	    style.setFont(font);
		return style;
	}
	public CellStyle createCellStyle(Font font){
		CellStyle style = workbook.createCellStyle();
		style.setWrapText(true);
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		style.setFont(font);
		return style;
	}
	
	public CellStyle createTitleCellStyle(){
	    // 创建单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        // 设置单元格的背景颜色为淡蓝色
        cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);
        // 设置填充字体的样式
     //   cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置单元格居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置单元格垂直居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 创建单元格内容显示不下时自动换行
        cellStyle.setWrapText(true);
        // 设置单元格字体样式
        Font font = workbook.createFont();
        // 字号
        font.setFontHeightInPoints((short) 12);
        // 加粗
        font.setBold(true);
        // 将字体填充到表格中去
        cellStyle.setFont(font);
        // 设置单元格边框为细线条（上下左右）
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        return cellStyle;
	}
	public Row getRow(Sheet sheet,int index) {
		Row row = sheet.getRow(index);
		
		if(row==null) {
			row = sheet.createRow(index);
		}
		return row;
	}
	public String getCellValue(XSSFCell cell) {
		String value = "";
		try {
			value = cell.getNumericCellValue() + "";
		} catch (java.lang.IllegalStateException e) {
			value = cell.getStringCellValue();
		}
		return value;
	}
	public Double getNumericCellValue(XSSFCell cell) {
		Double value = 0.0;
		try {
			value = cell.getNumericCellValue();
		} catch (java.lang.IllegalStateException e) {
			String text = cell.getStringCellValue();
			if(StringUtils.isNumeric(text)) {
				value = Double.valueOf(value);
			}
		}
		return value;
	}
	public Cell getCell(Row row,int cellIndex,CellStyle style){
		Cell hc = row.getCell((short)cellIndex);
		if(hc==null){
			hc = row.createCell(cellIndex);
		}
		hc.setCellStyle(style);
		return hc;
	}
	public Cell getCell(Row row,int cellIndex){
		Cell hc = row.getCell((short)cellIndex);
		if(hc==null){
			hc = row.createCell(cellIndex);
		}
		if(this.style==null) {
			this.style = createCellStyle();
		}
		hc.setCellStyle(this.style);
		return hc;
	}
	public Cell setCellValue(Cell hc,String value,CellStyle style){
		if(StringUtils.isEmpty(value)){
			hc.setCellValue("");
		}else{
			try{
				if(value.startsWith("0")&&!value.startsWith("0.")){
					hc.setCellValue(value);
				}else{
					hc.setCellValue(Integer.valueOf(value));
				}
			}catch(NumberFormatException e){
				try{
					hc.setCellValue(Double.valueOf(value));
				}catch(NumberFormatException e2){
					hc.setCellValue(value);
				}catch(NullPointerException e2){
					hc.setCellValue("");
				}
			}catch(NullPointerException e){
				hc.setCellValue("");
			}
		}
		hc.setCellStyle(style);
		return hc;
	}
	public Cell setCellValue(Cell hc,String value){
		if(StringUtils.isEmpty(value)){
			hc.setCellValue("");
		}else{
			try{
				if(value.startsWith("0")&&!value.startsWith("0.")){
					hc.setCellValue(value);
				}else{
					hc.setCellValue(Integer.valueOf(value));
				}
			}catch(NumberFormatException e){
				try{
					hc.setCellValue(Double.valueOf(value));
				}catch(NumberFormatException e2){
					hc.setCellValue(value);
				}catch(NullPointerException e2){
					hc.setCellValue("");
				}
			}catch(NullPointerException e){
				hc.setCellValue("");
			}
		}
		return hc;
	}
	public void addMergedRegion(Sheet hs,int top,int bottom,int left,int right,CellStyle style){
		 hs.addMergedRegion(new CellRangeAddress(top,bottom,left,right));
		 for(int i=top;i<=bottom;i++){
			 Row hr = getRow(hs, i);
			 for(int j=left;j<=right;j++){
				 getCell(hr, j,style);
			 }
		 }
	}
	public MergedResult isMergedRegion(Sheet sheet,int row ,int column) {
		int sheetMergeCount = sheet.getNumMergedRegions(); 
		for (int i = 0; i < sheetMergeCount; i++) { 
			CellRangeAddress range = sheet.getMergedRegion(i); 
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn(); 
			int firstRow = range.getFirstRow(); 
			int lastRow = range.getLastRow(); 
			if(row >= firstRow && row <= lastRow){
			if(column >= firstColumn && column <= lastColumn){
				return new MergedResult(true,firstRow+1,lastRow+1,firstColumn+1,lastColumn+1); 
			}
		}
		}
		return new MergedResult(false,0,0,0,0);
   }
   public String encodeFilename(String filename, HttpServletRequest request) {
		try {
			filename = URLEncoder.encode(filename, "UTF-8");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		return filename;
   }
}
